﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SSE657.BahMap.Common;

namespace SSE657.BahMap.DataAccess.SQLServer
{
    public class BAHDataSQLServerConnector : IBAHDataConnector
    {
        public IEnumerable<RateData> GetAreaRates(PayGrade grade, bool dependents)
        {
            using (BAHMapSqlServerEntities entities = new BAHMapSqlServerEntities())
            {
                string gradeStr = grade.ToString();
                IEnumerable<BAHRate> rates = entities.BAHRates.Where(r => r.Dependents == dependents && r.PayGrade == gradeStr).AsEnumerable();

                List<RateData> results = new List<RateData>();
                foreach (BAHRate rate in rates)
                {
                    results.Add(ConvertToRateData(rate));
                }
                return results;
            }
        }

        public double GetRate(PayGrade grade, bool dependents, int zipcode)
        {
            return GetRate(grade, dependents, GetMHA(zipcode));
        }

        public double GetRate(PayGrade grade, bool dependents, string MHA)
        {
            using (BAHMapSqlServerEntities entities = new BAHMapSqlServerEntities())
            {
                string gradeStr = grade.ToString();
                decimal result = entities.BAHRates.Single(r => r.Dependents == dependents && r.PayGrade == gradeStr && r.MHA == MHA).BAHRate1;
                return Convert.ToDouble(result);
            }
        }

        public void GetRateAndRange(PayGrade grade, bool dependents, int zipcode, out double rate, out double min, out double max)
        {
            GetRateAndRange(grade, dependents, GetMHA(zipcode), out rate, out min, out max);
        }

        public void GetRateAndRange(PayGrade grade, bool dependents, string mha, out double rate, out double min, out double max)
        {
            using (BAHMapSqlServerEntities entities = new BAHMapSqlServerEntities())
            {
                string gradeStr = grade.ToString();
                decimal rateResult = entities.BAHRates.Single(r => r.Dependents == dependents && r.PayGrade == gradeStr && r.MHA == mha).BAHRate1;
                rate = Convert.ToDouble(rateResult);
                decimal minResult = entities.BAHRates.Where(r => r.Dependents == dependents && r.PayGrade == gradeStr).Min(r => r.BAHRate1);
                min = Convert.ToDouble(minResult);
                decimal maxResult = entities.BAHRates.Where(r => r.Dependents == dependents && r.PayGrade == gradeStr).Max(r => r.BAHRate1);
                max = Convert.ToDouble(maxResult);

                // other ways of selecting MIN and MAX:
                //var queryMax = from item in entities.BAHRates
                //               where item.PayGrade == grade.ToString() && item.Dependents == dependents
                //               select item;
                //max = Convert.ToDouble(queryMax.Max(i => i.BAHRate1));
                //var queryMin = from item in entities.BAHRates
                //               where item.PayGrade == grade.ToString() && item.Dependents == dependents
                //               group item by item.PayGrade into g
                //               select new { BAHRateKey = g.Key, Min = g.Min(x => x.BAHRate1) };
                //min = Convert.ToDouble(queryMin.First().Min);
            }
        }

        public IEnumerable<RateData> GetGradeRates(bool dependents, int zipcode)
        {
            return GetGradeRates(dependents, GetMHA(zipcode));
        }

        public IEnumerable<RateData> GetGradeRates(bool dependents, string mha)
        {
            using (BAHMapSqlServerEntities entities = new BAHMapSqlServerEntities())
            {
                IEnumerable<BAHRate> rates = entities.BAHRates.Where(r => r.Dependents == dependents && r.MHA == mha).AsEnumerable();

                List<RateData> results = new List<RateData>();
                foreach (BAHRate rate in rates)
                {
                    results.Add(ConvertToRateData(rate));
                }
                return results;
            }
        }

        public IEnumerable<RateData> GetRates(int zipcode)
        {
            return GetRates(GetMHA(zipcode));
        }

        public IEnumerable<RateData> GetRates(string mha)
        {
            using (BAHMapSqlServerEntities entities = new BAHMapSqlServerEntities())
            {
                IEnumerable<BAHRate> rates = entities.BAHRates.Where(r => r.MHA == mha).AsEnumerable();

                List<RateData> results = new List<RateData>();
                foreach (BAHRate rate in rates)
                {
                    results.Add(ConvertToRateData(rate));
                }
                return results;
            }
        }

        #region Helper methods
        private string GetMHA(int zipcode)
        {
            using (BAHMapSqlServerEntities entities = new BAHMapSqlServerEntities())
            {
                return entities.MHA_ZIPs.Single(m => m.ZIPCode == zipcode).MHA;
            }
        }

        private RateData ConvertToRateData(BAHRate rate)
        {
            return new RateData()
            {
                Dependents = rate.Dependents,
                Rate = Convert.ToDouble(rate.BAHRate1),
                Grade = (PayGrade)Enum.Parse(typeof(PayGrade), rate.PayGrade),
                Area = ConvertToArea(rate.MHA1)
            };
        }

        private Area ConvertToArea(MHA mha)
        {
            List<int> zips = new List<int>();
            foreach (MHA_ZIPs record in mha.MHA_ZIPs)
            {
                zips.Add(record.ZIPCode);
            }
            return new Area()
            {
                MHA = mha.MHA1,
                ZIPs = zips
            };
        } 
        #endregion
    }
}
